/*
* Created on Nov 26, 2003
*
* To change the template for this generated file go to Window - Preferences -
* Java - Code Generation - Code and Comments
*/
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import nz.co.transparent.client.util.Constants;
import nz.co.transparent.client.db.PoolingDriverHandler;
/**
* @author johnz
*
*/
public class TestTransaction1 {
/**
*
*/
public TestTransaction1() {
super();
}
private void go() {
PoolingDriverHandler databaseConnectionPool =
new PoolingDriverHandler("client");
Connection connection1;
Connection connection2;
try {
// Create two JDBC connections to the database.
connection1 = DriverManager.getConnection(Constants.JDBC_URL);
connection2 = DriverManager.getConnection(Constants.JDBC_URL);
// Create two statements for each connection.
Statement s1 = connection1.createStatement();
Statement s2 = connection2.createStatement();
// Turn auto commit off.
connection1.setAutoCommit(false);
connection2.setAutoCommit(false);
// Create a table, populate it with two values, and commit
// the transaction.
s1.executeQuery("DROP TABLE Test");
s1.executeQuery("CREATE TABLE Test ( col INTEGER )");
s1.executeQuery("INSERT INTO Test VALUES ( 10 ), ( 20 )");
connection1.commit();
// After the commit, insert a third value into the table
// from connection1.
s1.executeQuery("INSERT INTO Test VALUES ( 15 )");
// This result will contain 3 values.
ResultSet result1 =
s1.executeQuery("SELECT * FROM Test ORDER BY col");
showResult("result1 after 1 commit on connection 1:", result1);
// Perform the same query, but from connection 2.
// This result will contain 2 values. It won't see the
// uncommitted insert made by connection 1.
ResultSet result2 =
s2.executeQuery("SELECT * FROM Test ORDER BY col");
showResult("result2 after 1 commit on connection 1:", result2);
// Commit the change make on connection 1
connection1.commit();
// Perform query on connection 2 again.
// This result will still only contain 2 values because
// connection 2 is isolated from all committed changes
// made to the database, until connection2 makes commit
//connection2.commit();
result2 = s2.executeQuery("SELECT * FROM Test ORDER BY col");
showResult("result2 after 2 commits on connection 1:", result2);
// Insert a value into the table on connection 2
s2.executeQuery("INSERT INTO Test VALUES ( 25 )");
// Commit the changes made in connection 2.
// A transaction error will occur here (conflict type 4)
// We selected from a table that had concurrent changes
// made to it.
connection2.commit();
connection1.close();
connection2.close();
System.out.println("TestTransaction1: ready");
} catch (SQLException se) {
System.out.println("SQL error: " + se.getMessage());
return;
}
}
private void showResult(String message, ResultSet resultSet) {
System.out.println("=====");
System.out.println(message);
int col;
try {
while (resultSet.next()) {
col = resultSet.getInt("col");
System.out.println("col = " + col);
}
} catch (SQLException se) {
System.out.println("Cannot get SQL connection");
return;
}
}
public static void main(String[] args) {
new TestTransaction1().go();
}
}